package edu.ncsu.csc.itrust.dao.mysql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.TreeSet;
import edu.ncsu.csc.itrust.DBUtil;
import edu.ncsu.csc.itrust.beans.LabProcedureBean;
import edu.ncsu.csc.itrust.beans.PersonnelBean;
import edu.ncsu.csc.itrust.beans.loaders.LabProcedureBeanLoader;
import edu.ncsu.csc.itrust.beans.LabTechProcedureBean;
import edu.ncsu.csc.itrust.dao.DAOFactory;
import edu.ncsu.csc.itrust.exception.DBException;

/**
 * A DAO for managing lab procedure codes. Database Access Object. All info coming into a DAO is already validated. 
 * Just worry about DB stuff here. Note that all DAOs need to have a DAOFactory with which to access other 
 * DAOs and to get connections. Also, every DAO must have a constructor with a DAOFactory as a parameter.
 */
public class LabProcedureDAO {
	private DAOFactory factory;
	private LabProcedureBeanLoader labProcedureLoader;

	/**
	 * The typical constructor.
	 * @param factory The {@link DAOFactory} associated with this DAO, which is used for obtaining SQL connections, etc.
	 */
	public LabProcedureDAO(DAOFactory factory) {
		this.factory = factory;
		labProcedureLoader = new LabProcedureBeanLoader();
	}
	
	/**
	 * Get a list of the lab procedures associated with a given patient.
	 * @param id The MID of the patient as a long.
	 * @return A java.util.List of LabProcedureBeans
	 * @throws DBException
	 */
	public List<LabProcedureBean> getLabProceduresForPatient(long id) throws DBException {
		Connection conn = null;
		PreparedStatement ps = null;

		try {
			if (id == 0L) throw new SQLException("PatientMID cannot be null");
			conn = factory.getConnection();
			ps = conn.prepareStatement("SELECT * FROM LabProcedure WHERE PatientMID = ? AND Rights = ? ORDER BY UpdatedDate DESC");
			ps.setLong(1, id);
			ps.setString(2, LabProcedureBean.Allow);
			ResultSet rs = ps.executeQuery();
			return labProcedureLoader.loadList(rs);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DBException(e);
		} finally {
			DBUtil.closeConnection(conn, ps);
		}
	}
	
	public List<LabProcedureBean> getLabProceduresForPatient(long id, String status) throws DBException{
		Connection conn = null;
		PreparedStatement ps = null;
		try{
			conn = factory.getConnection();
			ps = conn.prepareCall("SELECT * FROM LabProcedure WHERE PatientMID=? AND Status=? ORDER BY UpdatedDate");
			ps.setLong(1,id);
			ps.setString(2,status);
			ResultSet rs = ps.executeQuery();
			return labProcedureLoader.loadList(rs);
		}catch (SQLException e){
			e.printStackTrace();
			throw new DBException(e);
		}finally{
			DBUtil.closeConnection(conn,ps);
		}
	}
	
	/**
	 * Gets all the lab procedures for a given patient that occur within the next month.
	 * @param id The MID of the patient as a long.
	 * @return A java.util.List of LabProcedureBeans.
	 * @throws DBException
	 */
	public List<LabProcedureBean> getLabProceduresForPatientForNextMonth(long id) throws DBException {
		Connection conn = null;
		PreparedStatement ps = null;

		try {
			if (id == 0L) throw new SQLException("PatientMID cannot be null");
			conn = factory.getConnection();
			ps = conn.prepareStatement("SELECT * FROM LabProcedure WHERE PatientMID = ? AND Rights = ? AND Status = ? AND (DateDiff(SYSDATE(),UpdatedDate) <= 30) ORDER BY UpdatedDate DESC");
			ps.setLong(1, id);
			ps.setString(2, LabProcedureBean.Allow);
			ps.setString(3, LabProcedureBean.Completed);
			ResultSet rs = ps.executeQuery();
			return labProcedureLoader.loadList(rs);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DBException(e);
		} finally {
			DBUtil.closeConnection(conn, ps);
		}
	}
	
	/**
	 * Gets an individual lab procedure.
	 * @param id The ID of the lab procedure.
	 * @return A LabProcedureBean representing the procedure.
	 * @throws DBException
	 */
	public LabProcedureBean getLabProcedure(long id) throws DBException {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = factory.getConnection();
			ps = conn.prepareStatement("SELECT * FROM LabProcedure WHERE LaboratoryProcedureID = ?");
			ps.setLong(1, id);
			ResultSet rs = ps.executeQuery();
			rs.next();
			return labProcedureLoader.loadSingle(rs);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DBException(e);
		} finally {
			DBUtil.closeConnection(conn, ps);
		}
	}
	/**
	 * Gets all procedures for Patient
	 * @param mid patient id
	 * @return A java.util.List of LabProcedureBeans.
	 * @throws DBException
	 */
	public List<LabProcedureBean> getAllLabProceduresDate(long mid) throws DBException {
		Connection conn = null;
		PreparedStatement ps = null;

		try {
			if (mid == 0L) throw new SQLException("PatientMID cannot be null");
			conn = factory.getConnection();
			ps = conn.prepareStatement("SELECT * FROM LabProcedure WHERE PatientMID = ? ORDER BY UpdatedDate DESC");
			ps.setLong(1, mid);
			ResultSet rs = ps.executeQuery();
			return labProcedureLoader.loadList(rs);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DBException(e);
		} finally {
			DBUtil.closeConnection(conn, ps);
		}
	}
	/**
	 * This gets all the procedures for a particular patient on a particular office visit
	 * @param mid The MID of the patient.
	 * @param ovid The Office Visit ID.
	 * @return A java.util.List of LabProcedureBeans.
	 * @throws DBException
	 */
	public List<LabProcedureBean> getAllLabProceduresForDocOV(long mid, long ovid) throws DBException {
		Connection conn = null;
		PreparedStatement ps = null;

		try {
			if (mid == 0L) throw new SQLException("PatientMID cannot be null");
			conn = factory.getConnection();
			ps = conn.prepareStatement("SELECT * FROM LabProcedure WHERE PatientMID = ? AND OfficeVisitID = ? ORDER BY UpdatedDate DESC");
			ps.setLong(1, mid);
			ps.setLong(2, ovid);
			ResultSet rs = ps.executeQuery();
			return labProcedureLoader.loadList(rs);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DBException(e);
		} finally {
			DBUtil.closeConnection(conn, ps);
		}
	}
	
	/**
	 * This gets all the procedures for a particular patient on a particular office visit
	 * @param mid The MID of the patient.
	 * @param ovid The Office Visit ID.
	 * @return A java.util.List of LabProcedureBeans
	 * @throws DBException
	 */
	public List<LabProcedureBean> getAllLabProceduresForDocOV(long ovid) throws DBException {
		Connection conn = null;
		PreparedStatement ps = null;

		try {
			conn = factory.getConnection();
			ps = conn.prepareStatement("SELECT * FROM LabProcedure WHERE OfficeVisitID = ? ");
			ps.setLong(1, ovid);
			ResultSet rs = ps.executeQuery();
			return labProcedureLoader.loadList(rs);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DBException(e);
		} finally {
			DBUtil.closeConnection(conn, ps);
		}
	}
	
	/**
	 * Gets all lab procedures, period.
	 * @return A java.util.List of LabProcedureBeans.
	 * @throws DBException
	 */
	public List<LabProcedureBean> getAllLabProcedures() throws DBException {
		Connection conn = null;
		PreparedStatement ps = null;

		try {
			conn = factory.getConnection();
			ps = conn.prepareStatement("SELECT * FROM LabProcedure ORDER BY UpdatedDate ASC");
			ResultSet rs = ps.executeQuery();
			return labProcedureLoader.loadList(rs);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DBException(e);
		} finally {
			DBUtil.closeConnection(conn, ps);
		}
	}
	
	/**
	 * Gets the lab procedures for a given LHCP that occur within the next month.
	 * @param ovid The Office Visit ID conducted by the LHCP in question.
	 * @return A java.util.List of LabProcedureBeans.
	 * @throws DBException
	 */
	public List<LabProcedureBean> getLabProceduresForLHCPForNextMonth(long ovid) throws DBException {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			if (ovid == 0L) throw new SQLException("OfficeVisitID cannot be null");
			conn = factory.getConnection();
			ps = conn.prepareStatement("SELECT * FROM LabProcedure WHERE OfficeVisitID = ? AND Status = ? AND (DateDiff(SYSDATE(),UpdatedDate) <= 30) ORDER BY UpdatedDate DESC");
			ps.setLong(1, ovid);
			ps.setString(2, LabProcedureBean.Completed);
			ResultSet rs = ps.executeQuery();
			return labProcedureLoader.loadList(rs);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DBException(e);
		} finally {
			DBUtil.closeConnection(conn, ps);
		}
	}
	
	/**
	 * Inserts a lab procedure into the database.
	 * @param b The LabProcedureBean to be inserted.
	 * @return A long containing the ID of the newly inserted lab procedure bean.
	 * @throws DBException
	 */
	public long addLabProcedure(LabProcedureBean b) throws DBException {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			if (b.getPid() == 0L) throw new SQLException("PatientMID cannot be null");
			conn = factory.getConnection();
			ps = conn.prepareStatement("INSERT INTO LabProcedure (PatientMID, LaboratoryProcedureCode, Status, Commentary, Results, OfficeVisitID, Rights, Priority, LabTechMID) VALUES (?,?,?,?,?,?,?,?,?)");
			ps.setLong(1, b.getPid());
			ps.setString(2, b.getLoinc());
			ps.setString(3, b.getStatus());
			ps.setString(4, b.getCommentary());
			ps.setString(5, b.getResults());
			ps.setLong(6, b.getOvID());
			ps.setString(7, b.getRights());
			ps.setInt(8, b.getPriority());
			ps.setLong(9, b.getLtID());
			ps.executeUpdate();
			return DBUtil.getLastInsert(conn);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DBException(e);
		} finally {
			DBUtil.closeConnection(conn, ps);
		}
	}
	
	/**
	 * Updates an existing lab procedure.
	 * @param b The LabProcedureBean representing the procedure to be updated.
	 * @throws DBException
	 */
	public void updateLabProcedure(LabProcedureBean b) throws DBException {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			if (b.getPid() == 0L) throw new SQLException("PatientMID cannot be null");
			conn = factory.getConnection();
			ps = conn.prepareStatement("UPDATE LabProcedure SET Status = ?, Commentary = ?, Results = ?, UpdatedDate = ?, LabTechMID= ? WHERE LaboratoryProcedureID=?");
			ps.setString(1, b.getStatus());
			ps.setString(2, b.getCommentary());
			ps.setString(3, b.getResults());
			ps.setTimestamp(4, new java.sql.Timestamp(System.currentTimeMillis()));
			ps.setLong(5, b.getLtID());
			ps.setLong(6, b.getProcedureID());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DBException(e);
		} finally {
			DBUtil.closeConnection(conn, ps);
		}
	}
	
	/**
	 * Gets all the lab procedures that correspond to a particular LOINC.
	 * @param id The LOINC in question.
	 * @return A java.util.List of LabProcedureBeans.
	 * @throws DBException
	 */
	public List<LabProcedureBean> getAllLabProceduresLOINC(long id) throws DBException {
		Connection conn = null;
		PreparedStatement ps = null;

		try {
			if (id == 0L) throw new SQLException("PatientMID cannot be null");
			conn = factory.getConnection();
			ps = conn.prepareStatement("SELECT * FROM LabProcedure WHERE PatientMID = ? ORDER BY LaboratoryProcedureCode ASC");
			ps.setLong(1, id);
			ResultSet rs = ps.executeQuery();
			return labProcedureLoader.loadList(rs);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DBException(e);
		} finally {
			DBUtil.closeConnection(conn, ps);
		}
	}
	
	/**
	 * Updates the rights of a user on a given lab procedure.
	 * @param b The LabProcedureBean in question.
	 * @throws DBException
	 */
	public void updateRights(LabProcedureBean b) throws DBException {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			if (b.getPid() == 0L) throw new SQLException("PatientMID cannot be null");
			conn = factory.getConnection();
			ps = conn.prepareStatement("UPDATE LabProcedure SET Rights = ?, UpdatedDate = ? WHERE LaboratoryProcedureID=?");
			ps.setString(1, b.getRights());
			ps.setTimestamp(2, new java.sql.Timestamp(System.currentTimeMillis()));
			ps.setLong(3, b.getProcedureID());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DBException(e);
		} finally {
			DBUtil.closeConnection(conn, ps);
		}
	}
	
	/**
	 * Updates the priority of the provided lab procedure.
	 * @param labprocedure The ID of the lab procedure to update
	 * @param priority The new priority of the lab procedure
	 * @return int The value of the updated priority.
	 * @throws DBException
	 */
	public int updatePriority(long labprocedure, int priority) throws DBException{
		Connection conn = null;
		PreparedStatement ps = null;
		try{
			conn = factory.getConnection();
			ps = conn.prepareStatement("UPDATE LabProcedure SET Priority=? WHERE LaboratoryProcedureID=?");
			ps.setInt(1, priority);
			ps.setLong(2, labprocedure);
			ps.executeUpdate();
			return priority;
		}catch (SQLException e) {
			e.printStackTrace();
			throw new DBException(e);
		}finally {
			DBUtil.closeConnection(conn,ps);
			
		}
	}
	
	/**
	 * Returns the priority of a provided lab procedure.
	 * @param labprocedure The ID of the lab procedure to query.
	 * @return int The priority of the provided lab procedure 0 if error.
	 * @throws DBException 
	 */
	public int getPriority(long labprocedure) throws DBException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		try{
			conn = factory.getConnection();
			ps = conn.prepareStatement("SELECT Priority FROM LabProcedure WHERE LaboratoryProcedureID = ?");
			ps.setLong(1, labprocedure);
			ResultSet rs = ps.executeQuery();
			rs.next();
			return rs.getInt("Priority");
		}catch (SQLException e) {
			e.printStackTrace();
			throw new DBException(e);
		}finally{
			DBUtil.closeConnection(conn,ps);
		
		}
	}

	/**
	 * Updates the status of the provided lab procedure.
	 * @param id The ID of the lab procedure to update
	 * @param status The new status of the lab procedure.
	 * @return String The updated status null if error. 
	 * @throws DBException 
	 */
	public String updateStatus(long id, String status) throws DBException{
		Connection conn = null;
		PreparedStatement ps = null;
		try{
			conn = factory.getConnection();
			ps = conn.prepareStatement("UPDATE LabProcedure SET Status=? WHERE LaboratoryProcedureID=?");
			ps.setString(1, status);
			ps.setLong(2, id);
			ps.executeUpdate();
			return status;
		}catch (SQLException e) {
			e.printStackTrace();
			throw new DBException(e);
		}finally{
			DBUtil.closeConnection(conn,ps);
		}
	}
	
	/**
	 * Adds Comments to the provided lab procedure and sets the status as completed.
	 * @param procedureID The ID of the lab procedure to update.
	 * @param comments The string of comments to add to the provided lab procedure.
	 * @return String The status of the updated procedure.
	 * @throws SQLException.
	 */
	public String completeProcedure(long procedureID, String comments) throws DBException{
		Connection conn = null;
		PreparedStatement ps = null;
		try{
			conn = factory.getConnection();
			ps = conn.prepareStatement("UPDATE LabProcedure SET Status=? , Commentary=? WHERE LaboratoryProcedureID=?");
			ps.setString(1, "COMPLETED");
			ps.setString(2, comments);
			ps.setLong(3, procedureID);
			ps.executeUpdate();
			return "COMPLETED";
		}catch (SQLException e) {
			e.printStackTrace();
			throw new DBException(e);
		}finally{
			DBUtil.closeConnection(conn,ps);
			
		}
	}
	/**
	 * Generates and returns an unsorted list of lab procedures
	 * for the provided labtech MID.
	 * @param thid The labtechMID of the patient of interest.
	 * @return list The list of lab procedures for the provided labtech MID.
	 * @throws DBException
	 */
	public List<LabProcedureBean> getProceduresForTech(long techid) throws DBException{
		Connection conn = null;
		PreparedStatement ps = null;
		try{
			conn = factory.getConnection();
			ps = conn.prepareStatement("SELECT * FROM LabProcedure WHERE LabTechMID=?");
			ps.setLong(1, techid);
			ResultSet rs = ps.executeQuery();
			return labProcedureLoader.loadList(rs);
			
		}catch (SQLException e) {
			e.printStackTrace();
			throw new DBException(e);
		}finally{
			DBUtil.closeConnection(conn,ps);
		}
		
	}
	/**
	 * overloaded getProceduresForTech method, for use with getting only
	 * the procedures with the specified status sorted by timestamp
	 * @param status The specified Status
	 * @return list The list of procedures sorted on timestamp
	 */
	public List<LabProcedureBean> getProceduresForTech(long techid,String status) throws DBException{
		Connection conn = null;
		PreparedStatement ps = null;
		try{
			conn = factory.getConnection();
			ps = conn.prepareCall("SELECT * FROM LabProcedure WHERE LabTechMID=? AND Status=? ORDER BY UpdatedDate");
			ps.setLong(1,techid);
			ps.setString(2,status);
			ResultSet rs = ps.executeQuery();
			return labProcedureLoader.loadList(rs);
		}catch (SQLException e){
			e.printStackTrace();
			throw new DBException(e);
		}finally{
			DBUtil.closeConnection(conn,ps);
		}
		
	}
	/**
	 * Updates the assigned tech for the provided lab procedure ID
	 * @param procedureID The procedure ID of the procedure to update
	 * @param techmid The ID of the new assigned tech
	 * @return long The id of the newly assigned tech 0 if error
	 * @throws DBException
	 */
	public long updateAssignedTech(long procedureID,long techmid) throws DBException{
		Connection conn = null;
		PreparedStatement ps = null;
		try{
			conn = factory.getConnection();
			ps = conn.prepareStatement("UPDATE LabProcedure SET LabTechMID=? WHERE LaboratoryProcedureID=?");
			ps.setLong(1, techmid);
			ps.setLong(2, procedureID);
			ps.executeUpdate();
			return techmid;
		}catch (SQLException e) {
			e.printStackTrace();
			throw new DBException(e);
		}finally{
			DBUtil.closeConnection(conn,ps);
		}
	}
	/**
	 * Updates the procedure to include the results and changes status to pending
	 * @param procedureID The id of the procedure to update
	 * @param comments The String of comments to report
	 * @return String The updated results null if error
	 * @throws DBException 
	 */
	public String reportProcedureResults(long procedureID, String comments) throws DBException{
		Connection conn = null;
		PreparedStatement ps = null;
		try{
			conn = factory.getConnection();
			ps = conn.prepareStatement("UPDATE LabProcedure SET Status=? , Results=? WHERE LaboratoryProcedureID=?");
			ps.setString(1,"PENDING");
			ps.setString(2,comments);
			ps.setLong(3, procedureID);
			ps.executeUpdate();
			return comments;
		}catch (SQLException e) {
			e.printStackTrace();
			throw new DBException(e);
		}finally{
			DBUtil.closeConnection(conn,ps);
		}
	}
	/**
	 * Returns an unsorted list of pending lab procedures for the provided patientMID
	 * @param patientmid The MID of the patient of interest
	 * @return list The list of pending lab procedures for the provided patientMID
	 * @throws DBException
	 */
	public List<LabProcedureBean> getPatientPending(long patientmid) throws DBException{
		Connection conn = null;
		PreparedStatement ps = null;
		try{
			conn = factory.getConnection();
			ps = conn.prepareStatement("SELECT * FROM LabProcedure WHERE PatientMID=? AND Status=?");
			ps.setLong(1, patientmid);
			ps.setString(2, "PENDING");
			ResultSet rs = ps.executeQuery();
			return labProcedureLoader.loadList(rs);
		}catch (SQLException e) {
			e.printStackTrace();
			throw new DBException(e);
		}finally{
			DBUtil.closeConnection(conn,ps);
		}
	}
	/**
	 * Returns a list of Lab Technicians by name, with their associated number of assigned
	 * procedures by prioroty and a total number of procedures
	 * @return returnList The list of LabTechProcedureBeans for each tech.
	 * @throws DBException
	 */
	public TreeSet<LabTechProcedureBean> getAllLabTechWithProcedures() throws DBException{
		Connection conn = null;
		PreparedStatement ps = null;		
		//array lists for temp and return list 
		ArrayList<PersonnelBean> list = new ArrayList<PersonnelBean>();
		TreeSet<LabTechProcedureBean> returnSet = new TreeSet<LabTechProcedureBean>();
		try{
			conn = factory.getConnection();
			ps = conn.prepareStatement("SELECT Personnel.firstname,Personnel.lastname,Personnel.specialty,RoleRelation.mid FROM RoleRelation INNER JOIN Personnel ON RoleRelation.StandardMID=Personnel.MID WHERE Personnel.role=? ");
			ps.setString(1, "lt");
			ResultSet rs = ps.executeQuery();			
			//loop through all mid returned from query and process only tech mids
			while(rs.next()){
				PersonnelBean pbean = new PersonnelBean();				
				pbean.setFirstName(rs.getString("FirstName"));
				pbean.setLastName(rs.getString("LastName"));
				pbean.setSpecialty(rs.getString("specialty"));
				pbean.setMID(rs.getLong("MID"));
				list.add(pbean);
			}			
			//loop through list of techs and produce a LabTechProcedureBean with the required information.
			for(int i = 0 ; i<list.size() ;i++ ){
				List<LabProcedureBean> temp = getProceduresForTech(list.get(i).getMID());
				int p1 = 0;
				int p2 = 0;
				int p3 = 0;				
				for(int j=0;j<temp.size();j++){						
					switch (temp.get(j).getPriority()){						
						case 1:
							p1++;
							break;
						case 2:
							p2++;
							break;
						case 3:
							p3++;
							break;
					}						
				}
				returnSet.add(new LabTechProcedureBean(list.get(i).getFirstName(),list.get(i).getLastName(), list.get(i).getMID(),list.get(i).getSpecialty(),p1,p2,p3));
			}
			return returnSet;			
			//if error return null
			}catch (SQLException e) {
				e.printStackTrace();
				throw new DBException(e);
			}finally{
				DBUtil.closeConnection(conn, ps);
			}
		}
	
}
